package com.testingedu.jdbcDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.db.DBManager;
import com.testingedu.model.TResource;
import com.testingedu.utils.Format;

public class TResourceDao extends BaseDao<TResource>{
	public void add(TResource tResource) throws Exception{
		String sql = "insert into t_resource (name, is_pri, path, is_local, description,add_time) value(?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(tResource.getName());
		if(tResource.getIsPri() == null || tResource.getIsPri().length() == 0)
			tResource.setIsPri("n");
		else {
			tResource.setIsPri("y");
		}
		values.add(tResource.getIsPri());
		values.add(tResource.getPath());
		values.add(tResource.getIsLocal());
		if(tResource.getDescription() == null)
			tResource.setDescription("");
		values.add(tResource.getDescription());
		values.add(Format.formatDateDay(new Date()));
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}
	
	public void update(TResource tResource) throws Exception {
		String sql = "update t_resource tr set tr.name = ?,tr.is_pri=?,tr.description=? where tr.id = ?";
		List<Object> values = new ArrayList<Object>();
		values.add(tResource.getName());
		if(tResource.getIsPri() != null && tResource.getIsPri().length() > 0)
			tResource.setIsPri("y");
		else {
			tResource.setIsPri("n");
		}
		values.add(tResource.getIsPri());
		values.add(tResource.getDescription());
		values.add(tResource.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}
	
	public void list(QueryResult queryResult, TResource tResource) throws Exception{
		StringBuffer sql = new StringBuffer("select * from t_resource tr where 1=1");
		List<Object> values = new ArrayList<Object>();
		if(tResource != null) {
			if(tResource.getName() != null && tResource.getName().trim().length() > 0) {
				sql.append(" and name like ?");
				values.add("%"+tResource.getName()+"%");
			}
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		}
		finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0, sql.length() - 10), countValues);
			rs.next();
			queryResult.setAllCount(new Long(rs.getInt(1)));
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		finally {
			close();
		}
	}
	
	public static List<TResource> detailTypeIndex() {
		String sql = "select * from t_resource tr order by id desc limit 5";
		Connection conn = DBManager.getConnection();
		List<TResource> result = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			result = _resultSetToList(rs);
	
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
			}
		}
		return result;
	}
	
	public TResource info(int id) throws Exception{
		String sql = "select * from t_resource tr where tr.id = ?";
		List<TResource> result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		}
		finally {
			close();
		}
		if(result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}
	

	public void delete(int id) throws Exception {
		String sql = "delete from t_resource where id = ?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}
	public static List<TResource> _resultSetToList(ResultSet rs) throws Exception{
		List<TResource> result = new ArrayList<TResource>();
		try {
			while(rs.next()) {
				TResource tResource = new TResource();
				tResource.setId(rs.getInt("id"));
				tResource.setName(rs.getString("name"));
				tResource.setIsPri(rs.getString("is_pri"));
				tResource.setIsLocal(rs.getString("is_local"));
				tResource.setPath(rs.getString("path"));
				tResource.setDescription(rs.getString("description"));
				tResource.setAddTime(Format.formatDateDay(rs
						.getDate("add_time")));
				result.add(tResource);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}
}
